﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.Object;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
namespace DataAccess
{
    public class QLDonGNDao
    {
        private DataAccess _conn;
        public QLDonGNDao()
        {
            _conn = new DataAccess();
        }
        // Lay quyen han cua nguoi dang nhap
        public string ChucVuDao(int username)
        {
            DataTable dt = new DataTable();
            string sql = " Select quyen_nv from users where id='" + username + "'";
            dt = _conn.ExecuteSelectQueryString(sql);
            return dt.Rows[0]["quyen_nv"].ToString();
        }
        // danh sach hoa don cua nhan vien hoac nguoi quan ly
        public DataTable DanhSachHDDao(int user, string chucvu,string trangthai,string tgian)
        {
            DataTable dt = new DataTable();
            string sql = "Select h.*,nv.taikhoan_nv from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";
               
            }
            if (trangthai != null)
                sql += " and h.tinhtrang_gh='" + trangthai + "'";
            if (tgian != "")
            {
                int tg = Convert.ToInt32(tgian);
                sql += " and thoigian_cb<='" + tg + "'";
            }
            dt = _conn.ExecuteSelectQueryString(sql);
            return dt;
        }
        // Danh sach hoa don trong tin trang canh bao
        public DataTable DanhSachHDCBDao(int user, string chucvu, long time, string tgian)
        {
            DataTable dt = new DataTable();
            string sql = "Select h.*,nv.taikhoan_nv from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";

            }
            sql += " and ( ngay_gh - '" + time + "' <= thoigian_cb and ngay_gh - '" + time + "'>=0) and tinhtrang_gh='1'";
            if (tgian != "")
            {
                int tg = Convert.ToInt32(tgian);
                sql += " and thoigian_cb<='" + tg + "'";
            }
            dt = _conn.ExecuteSelectQueryString(sql);
            return dt;
        }
        //dem danh sach hoa don trong tinh trang canh bao
        public int CountHDCBDao(int user, string chucvu, long time)
        {
            DataTable dt = new DataTable();
            string sql = "Select * from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";

            }
            sql += " and ( ngay_gh - '" + time + "' <= thoigian_cb and ngay_gh - '" + time + "'>=0) and tinhtrang_gh='1'";
           
            dt = _conn.ExecuteSelectQueryString(sql);
            int count = dt.Rows.Count;
            return count;
        }
        //Lay thong tin chi tiet cua hoa don:
        public DataTable ChiTietHoaDon(string maHD)
        {
            DataTable dt = new DataTable();
            string sql = "Select hs.soluong_dh,s.ten_sp from hoadon_sanpham hs,sanpham s where s.ma_sp=hs.ma_sp and h.ma_hd='"+maHD+"'";
            dt = _conn.ExecuteSelectQueryString(sql);
            return dt;
        }
       // update trang thai don
        public void updateDao(string xacnhan, string maHD)
        {
            string sql = "";
            if (xacnhan == "T")
            {
                sql = "update hoadon set xacnhan='" + xacnhan + "', tinhtrang_gh='3' where ma_hd='" + maHD + "'";
            }
            else
            {
                sql = "update hoadon set xacnhan='" + xacnhan + "', tinhtrang_gh='1' where ma_hd='" + maHD + "'";
            }

            _conn.ExecuteNonQueryString(sql);
        }

        //do du lieu ra dataset
        public DataSet selectAllHDDao(int user, string chucvu, string trangthai, string tgian)
        {
            DataSet ds = new DataSet();
            string sql = "Select h.*,nv.taikhoan_nv from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";

            }
            if (trangthai != null)
                sql += " and h.tinhtrang_gh='" + trangthai + "'";
            if (tgian != "")
            {
                int tg = Convert.ToInt32(tgian);
                sql += " and thoigian_cb<='" + tg + "'";
            }
            ds = _conn.ExecuteDataSetQuery(sql, "hoadon");
            return ds;
        }
        // Đọc số lượng đơn
        public int countBillsDao(int user, string chucvu, string tinhtrang,DateTime date)
        {
            DataTable dt = new DataTable();
            string sql = "Select * from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";

            }
            if (tinhtrang != null && date==DateTime.MinValue)
            {
                sql += " and h.tinhtrang_gh='" + tinhtrang + "'";
            }
            if (date !=DateTime.MinValue)
            {
               sql += " and (CONVERT(varchar(10), h.ngay_dh, 103) like CONVERT(varchar(10), '" + date + "', 103))";
            }
            dt = _conn.ExecuteSelectQueryString(sql);
            int count = dt.Rows.Count;
            return count;
        }
        //Lấy thời gian nhắc lại thông báo
        public int getTimeAgainDao()
        {
            DataTable dt = new DataTable();
            string sql = " Select parameter_name from parameters where parameter_code='tgiannhclai'";
            dt = _conn.ExecuteSelectQueryString(sql);
            int tgian = Convert.ToInt32(dt.Rows[0]["parameter_name"]);
            return tgian;
        }
        //Lấy thời gian dừng cho form cảnh báo
        public int getTimeDelayDao()
        {
            DataTable dt = new DataTable();
            string sql = " Select parameter_name from parameters where parameter_code='tgiandung'";
            dt = _conn.ExecuteSelectQueryString(sql);
            int tgian = Convert.ToInt32(dt.Rows[0]["parameter_name"]);
            return tgian;
        }
        //update thoi gian nhac lai thong bao
        public void updateTImeAgainDao(string time)
        {
            string sql = " update parameters set parameter_name='" + time + "' where parameter_code='tgiannhclai'";
            _conn.ExecuteNonQueryString(sql);
        }
        //update thoi gian dung cho form canh bao
        public void updateTimeDelayDao(string time)
        {
            string sql = " update parameters set parameter_name='" + time + "' where parameter_code='tgiandung'";
            _conn.ExecuteNonQueryString(sql);
        }
        //Dem so hoa don canh bao moi
        public int CountHDCBMoiDao(int user, string chucvu, long time)
        {
            DataTable dt = new DataTable();
            string sql = "Select * from hoadon h, users nv where h.nhanvien_gh=nv.id ";
            if (chucvu == "N")
            {
                sql += " and h.nhanvien_gh='" + user + "'";

            }
            sql += " and ((ngay_gh - '" + time + "'<= thoigian_cb+1) and (ngay_gh - '" + time + "'>= thoigian_cb-1)) and tinhtrang_gh='1'";

            dt = _conn.ExecuteSelectQueryString(sql);
            int count = dt.Rows.Count;
            return count;
        }
        //chon am thanh
        public string SounDefaultDao()
        {
            DataTable dt = new DataTable();
            string sql = "Select parameter_name from parameters where parameter_code='sound_default' ";
            dt = _conn.ExecuteSelectQueryString(sql);
            string sound = dt.Rows[0]["parameter_name"].ToString();
            return sound;
        }
        //lay tat ca am thanh
        public DataTable SoundAllDao()
        {
            DataTable dt = new DataTable();
            string sql = "Select parameter_name from parameters where parameter_code='am_thanh'";
            dt = _conn.ExecuteSelectQueryString(sql);
            return dt;
        }
        //sua doi am thanh cai dat
        public void updateSoungDao(string filname)
        {
            string sql = " update parameters set parameter_name='" + filname + "' where parameter_code='sound_default'";
            _conn.ExecuteNonQueryString(sql);
        }
        //Update số lần lặp lại cảnh báo cho một đơn hàng
        public void updateCountAgainDao(string ct)
        {
            string sql = "update parameters set parameter_name='" + ct + "' where parameter_code='laplaiCB'";
            _conn.ExecuteNonQueryString(sql);
        }
        // Lay ra thoi gian lap lai
        public int getCountAgianCBDao()
        {
            DataTable dt = new DataTable();
            string sql = "Select parameter_name from parameters where parameter_code='laplaiCB'";
            dt = _conn.ExecuteSelectQueryString(sql);
            int count = Convert.ToInt32(dt.Rows[0]["parameter_name"].ToString());
            return count;
        }

    }
}
